package jdbc.exer.dao;

import jdbc.exer.domain.Student;
import jdbc.util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @author shkstart
 * @creat 2022-05-14:34
 */
public class StudentDao {
    public int add(Student s){
        String sql = "insert into s_student values(null,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] object={s.getName(),s.getSex(),s.getTelephone(),s.getQq(),s.getWechat(),s.getGradeId(),s.getEnterDate(),s.getFromCity(),s.getBirthday(),s.getAddress(),s.getPwd(),s.getIdCardPic()};
       return DButil.executeUpdate(sql,object);
    }
    public int update(Student s){
        String sql = "update s_student set sname=?,set fromcity=? where stuno=?";
        Object[] object={s.getName(),s.getFromCity(),s.getId()};
        return DButil.executeUpdate(sql,object);
    }
    public int del(long id){
        String sql = "delete s_student where stuno=?";
        Object[] object={id};
        return DButil.executeUpdate(sql,object);

    }
    //主键找对象
    public Student get(long id){
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            connection = DButil.getCon();
            Scanner sc = new Scanner(System.in);

            String sql = "select * from s_student where stuno=?";
            st = connection.prepareStatement(sql);
            st.setLong(1,id);
            rs = st.executeQuery();
            if(rs.next()){
                return new Student(rs.getLong(1),
                        rs.getString(2), rs.getString(3),
                        rs.getString(4),rs.getString(5),
                        rs.getString(6),rs.getLong(7),
                        rs.getDate(8),rs.getString(9),
                        rs.getDate(10),rs.getString(11),
                        rs.getString(12),rs.getString(13));
            }
        } catch (SQLException e) {
            DButil.handlerEx(e);
            //e.printStackTrace();
        } finally {
            DButil.close(rs, st, connection);
        }
        return null;
    }
    //查询全部对象
    public List<Student> findALL(){
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        List<Student> studentList = new ArrayList<>();
        try {
            connection = DButil.getCon();
            Scanner sc = new Scanner(System.in);

            String sql = "select * from s_student ";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            rs = st.executeQuery();
            while(rs.next()){
                studentList.add(new Student(rs.getLong(1),
                        rs.getString(2), rs.getString(3),
                        rs.getString(4),rs.getString(5),
                        rs.getString(6),rs.getLong(7),
                        rs.getDate(8),rs.getString(9),
                        rs.getDate(10),rs.getString(11),
                        rs.getString(12),rs.getString(13)));
            }
        } catch (SQLException e) {
            DButil.handlerEx(e);
            //e.printStackTrace();
        } finally {
            DButil.close(rs, st, connection);
        }
        return studentList;
    }

    public List<Student> search(int pageNo,int pageSize) {
        String sql = "select * from s_student order by stuno asc limit ?,?";
        List<Student> datas = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = DButil.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1,(pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                datas.add(new Student(rs.getLong(1),
                        rs.getString(2), rs.getString(3),
                        rs.getString(4),rs.getString(5),
                        rs.getString(6),rs.getLong(7),
                        rs.getDate(8),rs.getString(9),
                        rs.getDate(10),rs.getString(11),
                        rs.getString(12),rs.getString(13)));
            }
        } catch (SQLException e) {
            DButil.handlerEx(e);
            //e.printStackTrace();
        } finally {
            DButil.close(rs, ps, con);
        }
        return datas;
    }
}
